clear all
set more off
cd "/Users/yasenov/Dropbox (IPL)/RefugeeBan/data/crime"

capture log close
log using "../../logs/crime", replace

*********
*********
*********

// load data
use "offenses_known_yearly_2010_2018.dta"

// clean up
keep ori year fips_state_county_code actual_* tot_clr*
rename fips_state_county_code fips

// keep only agencies which report throughout the entire sample
unique ori
merge m:1 ori using "ori_dictionary.dta", keep(3) nogen
sum
unique ori

// THIS IS NEW - AFTER THE RR AT APSR
bysort ori: gen v = _N
tab v
keep if v == 9

// aggregate to fips-month level
collapse (sum) actual_*  tot_clr*, by(year fips)
sum

// remove missing, Puerto Rico, Guam
count if fips == "" 
count if substr(fips,3,3) == "999"
drop if fips == "" | substr(fips,3,3) == "999"
drop if substr(fips,1,2) == "72" | substr(fips,1,2) == "66" | substr(fips,1,2) == "57"

// get county and state names
merge m:1 fips using "../CityMapping/county_dict.dta", keep(1 3)
assert _merge == 3
drop _merge

/*
// add lables
do "../../code/data_clean/crime_labels.do"
*/

// clean and save
foreach var of varlist actual_murder-actual_index_total {
	local name = substr("`var'",8,.)
	rename `var' `name'
}

keep state* county* fips year theft_total robbery_total rape_total murder mtr_veh_theft_total burg_total assault_aggravated ///
	tot_clr_theft_total tot_clr_robbery_total tot_clr_rape_total tot_clr_murder tot_clr_mtr_veh_theft_total tot_clr_burg_total tot_clr_assault_aggravated

order *, alpha
order state* count* fips year
sort state_fips county_fips year

gen property_crime = mtr_veh_theft_total + burg_total + theft_total, after(year)
gen violent_crime = assault_aggravated + robbery_total + murder + rape_total, after(property_crime)

gen cleared_property_crime = tot_clr_mtr_veh_theft_total + tot_clr_burg_total + tot_clr_theft_total
gen cleared_violent_crime = tot_clr_assault_aggravated + tot_clr_robbery_total + tot_clr_murder + tot_clr_rape_total

drop tot_clr*

desc
sum

tab year, m

qui compress
isid fips year

save "crime.dta", replace
capture log close
